package studentInfo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;

public class StudentDataQueryBean {
	private DatabaseBean student=new DatabaseBean();
	private String sql;
	private String tablename="student";
	private String tablecols="student(Sno,Sname,Ssex,Sclassid,Smajor,Stype,SenrollDate,SacademicStructure,Campus)";
	public StudentDataBean[] overuseQuery(String sql) throws SQLException {//代码复用函数
		//DatabaseBean.connect();
		ResultSet rs=student.executeQuery(sql);//此处等数据库文件出来后补充
		rs.last();
		StudentDataBean stu[]=new StudentDataBean[rs.getRow()];//数组对象的指针
		rs.first();
		int i=0;
		do
		{
			StudentDataBean studentdata=new StudentDataBean();
			studentdata.setStudentId(rs.getString("Sno"));
			studentdata.setName(rs.getString("Sname"));
			studentdata.setGender(rs.getString("Ssex"));
			studentdata.setClassId(rs.getString("Sclassid"));
			studentdata.setMajor(rs.getString("Smajor"));
			studentdata.setType(rs.getString("Stype"));
			studentdata.setEnrollDate(rs.getDate("SenrollDate"));
			studentdata.setAcademicStructure(rs.getInt("SacademicStructure"));
			studentdata.setCampus(rs.getString("Campus"));
			stu[i]=studentdata;
			i++;
		}while(rs.next());
		return stu;
	}
	public StudentDataBean[] queryAllData() throws SQLException {
		//TODO
		sql="select * from "+tablename+";";
		return overuseQuery(sql);
	}
	
	public StudentDataBean[] queryById(String studentId) throws SQLException {
		sql="select * from "+tablename+" where Sno='"+studentId+"';";
		return overuseQuery(sql);
	}
	
	public StudentDataBean[] queryByName(String name) throws SQLException {
		sql="select * from "+tablename+" where Sname='"+name+"';";
		return overuseQuery(sql);
	}
	
	public boolean addData(StudentDataBean student) {
		
		SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
		String date =sdf.format(student.getEnrollDate());
		sql="insert into "+tablecols+" values"
				+"('"+student.getStudentId()+"','"+student.getName()+"','"+student.getGenderString()+"','"+student.getClassId()
				+"','"+student.getMajor()+"','"+student.getType()+"','"+date+"',"+student.getAcademicStructure()
				+",'"+student.getCampus()+"');";//注意int位置不加''
		//DatabaseBean.connect();
		return this.student.executeUpdate(sql);
	}
	
	public int addData(StudentDataBean[] students) {
		int num=0;
		for(StudentDataBean student: students)
		{
			if(addData(student)) num++;
		}
		return num;
	}
	
	public boolean modData(String studentId,StudentDataBean newStudent) {
		if(newStudent.getStudentId()!=studentId)
			return false;
		else
		{
			SimpleDateFormat sdf =new SimpleDateFormat("yyyy-MM-dd");
			String date =sdf.format(newStudent.getEnrollDate());
			sql = String.format("update %s set Sname='%s',Ssex='%s',Sclassid='%s',"
					+ "Smajor='%s',Stype='%s',SenrollDate='%s',"
					+ "SacademicStructure=%d,Campus='%s' "
					+ "where Sno='%s'"
					,tablename
					,newStudent.getName()
					,newStudent.getGenderString()
					,newStudent.getClassId()
					,newStudent.getMajor()
					,newStudent.getType()
					,date
					,newStudent.getAcademicStructure()
					,newStudent.getCampus()
					,studentId
					);
			
			return this.student.executeUpdate(sql);
		}
	}
	
	public boolean delData(String studentId) {
		sql="delete from "+tablename+" WHERE Sno='"+studentId+"';";
		return this.student.executeUpdate(sql);
	}
	
}
